cd C:\Users\alen_\Dropbox\burning_glass\data

**************************
**** Imputed employment at the occupation level (SOC - ONET)


import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_employment, replace

u county_naics3_employment, clear

keep if county<=20000
joinby naics using onet_naics3_share

g emp=month2_emplvl*share

collapse (sum) emp, by(county onet)

save temp1, replace 

u county_naics3_employment, clear

keep if county>20000 & county<=40000
joinby naics using onet_naics3_share

g emp=month2_emplvl*share

collapse (sum) emp, by(county onet)

save temp2, replace


u county_naics3_employment, clear

keep if county>40000 & county<=60000
joinby naics using onet_naics3_share

g emp=month2_emplvl*share

collapse (sum) emp, by(county onet)

save temp3, replace

clear all

append using temp1 
append using temp2 
append using temp3 

merge m:1 county using county_metrofips
keep if _merge==3
drop _merge

ren emp emp_county 
bysort metrofips onet: egen emp=total(emp_county)

merge m:1 metrofips using metrofips_population
keep if _merge==3
drop _merge

keep county metrofips onet emp emp_county

save onet_county_metrofips_emp_2018_q1, replace


*** Total employment 

import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2018_q1, replace


u county_emp_2018_q1, clear

ren month2_emplvl emp_msa 

merge m:1 county using county_metrofips
keep if _merge==3
drop _merge 

collapse (sum) emp_msa, by(metrofips)

save metrofips_emp_2018_q1, replace


************************ Industry level employment


** 2019 annual 
import delimited 2019.annual.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"


collapse (mean) annual_avg_wkly_wage [w=annual_avg_emplvl], by(area_fips industry_code)

ren annual_avg_wkly_wage avg_wkly_wage

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings, replace



*** 2013 Q1 

import delimited 2013.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2013_q1, replace


** 2013 Q2 
import delimited 2013.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2013_q2, replace


** 2013 Q3
import delimited 2013.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2013_q3, replace


** 2013 Q4
import delimited 2013.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2013_q4, replace



*** 2014 Q1 

import delimited 2014.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2014_q1, replace


** 2014 Q2 
import delimited 2014.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2014_q2, replace


** 2014 Q3
import delimited 2014.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2014_q3, replace


** 2014 Q4
import delimited 2014.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2014_q4, replace




*** 2015 Q1 

import delimited 2015.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2015_q1, replace


** 2015 Q2 
import delimited 2015.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2015_q2, replace


** 2015 Q3
import delimited 2015.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2015_q3, replace


** 2015 Q4
import delimited 2015.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2015_q4, replace

*** 2016 Q1 

import delimited 2016.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2016_q1, replace


** 2016 Q2 
import delimited 2016.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2016_q2, replace


** 2016 Q3
import delimited 2016.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2016_q3, replace


** 2016 Q4
import delimited 2016.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2016_q4, replace


*** 2017 Q1 

import delimited 2017.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2017_q1, replace


** 2017 Q2 
import delimited 2017.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2017_q2, replace


** 2017 Q3
import delimited 2017.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2017_q3, replace


** 2017 Q4
import delimited 2017.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2017_q4, replace

*** 2018 Q1 

import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2018_q1, replace



*** 2018 Q2 

import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2018_q2, replace



*** 2018 Q3 

import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2018_q3, replace



*** 2018 Q4 

import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2018_q4, replace



*** 2019 Q1 

import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2019_q1, replace



*** 2019 Q2 

import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2019_q2, replace


*** 2019 Q3 

import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2019_q3, replace


*** 2019 Q4 

import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2019_q4, replace


*** 2020 Q1 
import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2020_q1, replace


*** 2020 Q2 

import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2020_q2, replace


**** 2020 Q3 


import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2020_q3, replace


*** 2020 Q4 


import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2020_q4, replace


** 2021 Q1 



import delimited 2021.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2021_q1, replace


*** 2021 Q2 


import delimited 2021.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2021_q2, replace


*** 2021 Q3 


import delimited 2021.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2021_q3, replace


*** 2021 Q4

import delimited 2021.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2021_q4, replace



*** 2022 Q1

import delimited 2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2022_q1, replace


*** 2022 Q2

import delimited 2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2022_q2, replace


*** 2022 Q3

import delimited 2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2022_q3, replace


import delimited 2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2022_q4, replace



import delimited 2023.q1-q2.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2023_q1, replace


import delimited 2023.q1-q2.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2023_q2, replace



import delimited 2023.q1-q3.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2023_q3, replace


import delimited 2023.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2023_q4, replace


import delimited 2024.q1-q1.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (mean) avg_wkly_wage [w=month2_emplvl], by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_earnings_2024_q1, replace



**** Employment data 


import delimited 2015.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2015_q1, replace


import delimited 2015.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2015_q4, replace


import delimited 2016.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2016_q1, replace



import delimited 2016.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2016_q2, replace


import delimited 2016.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2016_q4, replace


import delimited 2017.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2017_q1, replace


import delimited 2017.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2017_q4, replace


import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2018_q1, replace


import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2018_q2, replace


import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2018_q3, replace



import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2018_q4, replace


import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2019_q1, replace


import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2019_q2, replace


import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2019_q3, replace



import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2019_q4, replace



import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2020_q1, replace

import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2020_q2, replace

import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2020_q3, replace


import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2020_q4, replace



import delimited 2021.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2021_q1, replace


import delimited 2021.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2021_q2, replace


import delimited 2021.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2021_q3, replace


import delimited 2021.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2021_q4, replace

import delimited 2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2022_q1, replace


import delimited 2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2022_q2, replace


import delimited 2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2022_q3, replace


import delimited 2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2022_q4, replace



import delimited 2023.q1-q2.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2023_q1, replace


import delimited 2023.q1-q2.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==2

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2023_q2, replace


import delimited 2023.q1-q3.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==3

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2023_q3, replace


import delimited 2023.q1-q4.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2023_q4, replace



import delimited 2024.q1-q1.singlefile.csv, clear

keep if agglvl_code==75

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics3) force 

sort county naics3

save county_naics3_emp_2024_q1, replace



***
import delimited 2017.q1-q4.singlefile.csv, clear

keep if agglvl_code==74

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics2) force 

sort county naics2

save county_naics2_emp_2017_q1, replace


import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==74

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics2) force 

sort county naics2

save county_naics2_emp_2019_q4, replace


import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==74

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics2) force 

sort county naics2

save county_naics2_emp_2020_q1, replace


import delimited C:\Users\alen_\Dropbox\covid_move\data\qcew\2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==74

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips industry_code)

destring area_fips, g(county) force

destring industry_code, g(naics2) force 

sort county naics2

save county_naics2_emp_2022_q4, replace




*** Total employment 



***

import delimited 2015.q1-q4.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==4

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2015_q4, replace

import delimited 2016.q1-q4.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2016_q1, replace


import delimited 2017.q1-q4.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2017_q1, replace


import delimited 2018.q1-q4.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2018_q1, replace



import delimited 2019.q1-q4.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2019_q1, replace



import delimited 2020.q1-q4.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2020_q1, replace


import delimited 2022.q1-q4.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2022_q1, replace



import delimited 2023.q1-q4.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2023_q1, replace


import delimited 2024.q1-q1.singlefile.csv, clear

keep if agglvl_code==71

keep if own_code==5

drop if disclosure_code=="N"

keep if qtr==1

collapse (sum) month2_emplvl, by(area_fips)

destring area_fips, g(county) force

sort county

save county_emp_2024_q1, replace



*** MSA 

*** By industry by MSA 


u county_naics3_emp_2018_q1, clear

ren month2_emplvl emp_msa 

merge m:1 county using county_metrofips
keep if _merge==3
drop _merge 

collapse (sum) emp_msa, by(naics3 metrofips)

drop if naics3==999
drop if metrofips==.
save metrofips_naics3_emp_2018_q1, replace



u county_emp_2017_q1, clear

ren month2_emplvl emp_msa 

merge m:1 county using county_metrofips
keep if _merge==3
drop _merge 

collapse (sum) emp_msa, by(metrofips)

save metrofips_emp_2017_q1, replace



u county_emp_2018_q1, clear

ren month2_emplvl emp_msa 

merge m:1 county using county_metrofips
keep if _merge==3
drop _merge 

collapse (sum) emp_msa, by(metrofips)

save metrofips_emp_2018_q1, replace


u county_emp_2019_q1, clear

ren month2_emplvl emp_msa 

merge m:1 county using county_metrofips
keep if _merge==3
drop _merge 

collapse (sum) emp_msa, by(metrofips)

save metrofips_emp_2019_q1, replace